package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import connection.ConnectionManager;
import domain.User;
import domain.VideoUser;

public class JDBCUserDAO implements IUserDAO {

	Connection conn;
	@Override
	public List<User> getAllUser() {
		List<User> users =new LinkedList<User>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios");
			User user = null;
			while(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
				users.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * 
	 * @param ID
	 * @return
	 */
	private List<Integer> getVideosByUser(int ID) {
		List<Integer> videos = new LinkedList<Integer>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT video_vid FROM video_usuarios WHERE  usuarios_uid = '" + ID + "'");			
			while(rs.next()){
					videos.add(rs.getInt(1));
			}
			}catch (SQLException e) {
				e.printStackTrace();
			}
		return videos;
	}

	/**
	 * 
	 * @param ID
	 * @return
	 */
	@Override
	public List<VideoUser> getVideosUsersOrderByDateDesc() {
		List<VideoUser> videos = new LinkedList<VideoUser>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT video_vid,usuarios_uid FROM video_usuarios ORDER BY fecha DESC");			
			while(rs.next()){
					videos.add(new VideoUser(rs.getInt(1),rs.getInt(2)));
			}
			}catch (SQLException e) {
				e.printStackTrace();
			}
		return videos;
	}
	
	private List<Integer> getFriendsByID(int ID) {
		List<Integer> friends = new LinkedList<Integer>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT usuarios_uid1 FROM usuarios_has_usuarios WHERE  usuarios_uid = '" + ID + "'");			
			while(rs.next()){
				friends.add(rs.getInt(1));
			}
			}catch (SQLException e) {
				e.printStackTrace();
			}
		return friends;
	}

	@Override
	public User getUserByLogin(String login) {
		User user = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE login = '" + login + "'");
			if(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}
	
	public User getUserById(Integer id) {
		User user = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE uid = '" + id + "'");
			if(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}

	@Override
	public List<User> getAllUserByName(String name) {
		List<User> users = new LinkedList<User>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE nombre LIKE '%" + name + "%'");
			User user = null;
			while(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
				users.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public String getNameById(Integer idUser) {
		String name = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT nombre,apellidos FROM usuarios WHERE uid =" + idUser);
			if(rs.next()){
				name = rs.getString(1)+" "+rs.getString(2);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}

	
	public String getLoginById(Integer idUser) {
		String login = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT login FROM usuarios WHERE uid =" + idUser);
			if(rs.next()){
				login = rs.getString(1);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return login;
	}

	@Override
	public List<User> getAllUserByNameYearGenre(String name, Integer year,String genre) {
		List<User> users = new LinkedList<User>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			// TODO Cuando en l base de datos haya edad
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE nombre LIKE '%" + name + "%' AND anio='"+year+"' AND generosFavoritos LIKE '%"+genre+"%'");
			//ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE nombre LIKE '%" + name + "%' AND generosFavoritos LIKE '%"+genre+"%'");
			User user = null;
			while(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
				users.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public List<User> getAllUserByNameYear(String name, Integer year) {
		List<User> users = new LinkedList<User>();
		try {
		conn = ConnectionManager.getInstance().openConnection();
		Statement stmt = conn.createStatement();
		// TODO Cuando en l base de datos haya edad
		ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE nombre LIKE '%" + name + "%' AND anio='"+year+"'");
		//ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE nombre LIKE '%" + name + "%'");
		User user = null;
		while(rs.next()){
			List<Integer> friends= getFriendsByID(rs.getInt(1));
			List<Integer> videos = getVideosByUser(rs.getInt(1));
			user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
			users.add(user);
		}
		rs.close();
		stmt.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return users;
	}

	@Override
	public List<User> getAllUserByYearGenre(Integer year, String genre) {
		List<User> users = new LinkedList<User>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			// TODO Cuando en la base de datos haya edad
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE anio='"+year+"' AND generosFavoritos LIKE '%"+genre+"%'");
			//ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE generosFavoritos LIKE '%"+genre+"%'");
			User user = null;
			while(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
				users.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public List<User> getAllUserByNameGenre(String name, String genre) {
		List<User> users = new LinkedList<User>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE nombre LIKE '%" + name + "%' AND generosFavoritos LIKE '%"+genre+"%'");
			User user = null;
			while(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
				users.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public List<User> getAllUserByYear(Integer year) {
		// TODO Cuando haya edad en la base de datos
		List<User> users = new LinkedList<User>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE anio='"+year+"'");
			//ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios");
			User user = null;
			while(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
				users.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public List<User> getAllUserByGenre(String genre) {
		List<User> users = new LinkedList<User>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM usuarios WHERE generosFavoritos LIKE '%"+genre+"%'");
			User user = null;
			while(rs.next()){
				List<Integer> friends= getFriendsByID(rs.getInt(1));
				List<Integer> videos = getVideosByUser(rs.getInt(1));
				user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getString(9), rs.getString(10), rs.getInt(11), rs.getString(12),rs.getString(13),rs.getString(14), friends, videos);
				users.add(user);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}

	@Override
	public boolean createAcount(User usuario) {
		try{
			conn = ConnectionManager.getInstance().openConnection();
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO usuarios (nombre,apellidos,login,pass,sexo,ciudad,provincia,pais,email,anio,avatar,actoresFavoritos,generosFavoritos) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pstmt.setString(1, usuario.getName());
			pstmt.setString(2, usuario.getSurname());
			pstmt.setString(3, usuario.getLogin());
			pstmt.setString(4, usuario.getPassword());
			pstmt.setString(5, usuario.getSex());
			pstmt.setString(6, usuario.getCity());
			pstmt.setString(7, usuario.getProvince());
			pstmt.setString(8, usuario.getCountry());
			pstmt.setString(9, usuario.getEmail());
			pstmt.setInt(10, usuario.getBirthday());
			if(usuario.getAvatar()!=null)
			{
				pstmt.setString(11, usuario.getAvatar());
			}
			else
			{
				pstmt.setString(11, "no_avatar.png");
			}
			pstmt.setString(12, usuario.getFavoriteActor());
			pstmt.setString(13, usuario.getFavoriteGenre());			
			pstmt.executeUpdate();			
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
		
	}

	@Override
	public boolean addFriend(Integer idUser, Integer idUserAdd) {
		try{
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt =conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT * FROM usuarios_has_usuarios WHERE usuarios_uid="+idUser+" AND usuarios_uid1="+idUserAdd);
			if(!rs.next())
			{
				PreparedStatement pstmt = conn.prepareStatement("INSERT INTO usuarios_has_usuarios (usuarios_uid,usuarios_uid1) VALUES (?,?)");
				pstmt.setInt(1, idUser);
				pstmt.setInt(2, idUserAdd);
				pstmt.executeUpdate();
			}
			else
			{
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public List<Integer> getInvitations(Integer idUser) {
		List<Integer> Invitations=new LinkedList<Integer>();
		try{
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt =conn.createStatement();
			Statement stmt1 =conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT usuarios_uid FROM usuarios_has_usuarios WHERE usuarios_uid1="+idUser);
			ResultSet rs1;
			while(rs.next())
			{
				Integer friendId=rs.getInt(1);
				rs1=stmt1.executeQuery("SELECT * FROM usuarios_has_usuarios WHERE usuarios_uid="+idUser+" AND usuarios_uid1="+friendId);
				if(!rs1.next())
				{
					Invitations.add(friendId);					
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return Invitations;
		}
		return Invitations;
	}

}
